/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package RoomTransaction;

import Admission.AdmissionBean;
import Hostel.HostelBean;
import Room.RoomBean;
import User.UserBean;
import java.sql.*;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con = null;
    public static int add(RoomTransactionBean rtb)throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into ROOM_TRANSACTION values(roomtransactionid.nextval,?,?,?,?,sysdate, sysdate,?,?)");
            pre.setInt(1, rtb.getHostelId());
            pre.setInt(2, rtb.getRoomId());
            pre.setInt(3, rtb.getRoomStatus());
            pre.setInt(4, rtb.getAdmissionId());
            pre.setInt(5, rtb.getUserId());
            pre.setDate(6, null);
            i=pre.executeUpdate();
        }catch(Exception e){
            System.out.println(e);
        }finally{
           con.close();
        }return i;
    }
    public static int edit(RoomTransactionBean rtb) throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("update ROOM_TRANSACTION set HOSTEL_ID=?, ROOM_ID=?, ROOM_STATUS=?, ADMISSION_ID=?, sysdate, ENTRY_DATE=sysdate, USER_ID=?,WITHDRAW_DATE=? where ROOM_TRANSACTION_ID=?");
            pre.setInt(1, rtb.getHostelId());
            pre.setInt(2, rtb.getRoomId());
            pre.setInt(3, rtb.getRoomStatus());
            pre.setInt(4, rtb.getAdmissionId());
            pre.setInt(5, rtb.getUserId());
            pre.setDate(6, null);
            pre.setInt(7, rtb.getRoomtransId());
            i=pre.executeUpdate();
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }
    public static int delete(int id)throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("delete from ROOM_TRANSACTION where ROOM_TRANSACTION_ID=?");
            pre.setInt(1, id);
            i=pre.executeUpdate();
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }
    public static Vector viewAll()throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from ROOM_TRANSACTION left join HOSTEL on ROOM_TRANSACTION.HOSTEL_ID=hostel.HOSTEL_ID left join ROOM on ROOM_TRANSACTION.ROOM_ID=room.ROOM_ID left join ADMISSION on ROOM_TRANSACTION.ADMISSION_ID=ADMISSION.ADMISSION_ID left join users on ROOM_TRANSACTION.USER_ID=users.USER_ID order by ROOM_TRANSACTION_id");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                RoomTransactionBean rtb=new RoomTransactionBean();
                HostelBean hb=new HostelBean();
                RoomBean rb=new RoomBean();
                AdmissionBean ab=new AdmissionBean();
                UserBean ub=new UserBean();
                rtb.setRoomtransId(rs.getInt(1));
                rtb.setHostelId(rs.getInt(2));
                rtb.setRoomId(rs.getInt(3));
                rtb.setRoomStatus(rs.getInt(4));
                rtb.setAdmissionId(rs.getInt(5));
                rtb.setDoa(rs.getDate(6));
                rtb.setEntryDate(rs.getDate(7));
                rtb.setUserId(rs.getInt(8));
                rtb.setWithdrawDate(rs.getDate(9));
                hb.setHotelId(rs.getInt(10));
                hb.setHostelName(rs.getString(11));
                hb.setHostelType(rs.getString(12));
                hb.setHostelAddress(rs.getString(13));
                hb.setWardenId(rs.getInt(14));
                hb.setMobile(rs.getString(15));
                hb.setPhone(rs.getString(16));
                hb.setEntryDate(rs.getDate(17));
                rtb.setHostelBean(hb);
                rb.setRoomId(rs.getInt(18));
                rb.setHostelId(rs.getInt(19));
                rb.setRoomNo(rs.getString(20));
                rb.setBedNumber(rs.getString(21));
                rb.setBedStatus(rs.getInt(22));
                rb.setEntryDate(rs.getDate(23));
                rb.setUniqueRoomKey(rs.getString(24));
                rb.setFees(rs.getInt(25));
                rtb.setRoomBean(rb);
                ab.setAddmissionId(rs.getInt(26));
                ab.setStudentEmail(rs.getString(27));
                ab.setStudentMobile(rs.getString(28));
                ab.setRollNumber(rs.getString(29));
                ab.setStudentSession(rs.getString(30));
                ab.setCourseId(rs.getInt(31));
                ab.setApplicationNo(rs.getString(32));
                ab.setAdmissionDate(rs.getDate(33));
                ab.setEntryDate(rs.getDate(34));
                ab.setEntryEmail(rs.getString(35));
                ab.setSemester(rs.getInt(36));
                rtb.setAdmissionBean(ab);
                ub.setUserId(rs.getInt(37));
                ub.setDor(rs.getDate(38));
                ub.setPassword(rs.getString(39));
                ub.setUserType(rs.getString(40));
                ub.setEmail(rs.getString(41));
                ub.setEmployeeName(rs.getString(42));
                ub.setDob(rs.getDate(43));
                ub.setEntryDate(rs.getDate(44));
                ub.setUserStatus(rs.getInt(45));
                ub.setEntryEmail(rs.getString(46));
                rtb.setUserBean(ub);
                list.add(rtb);
            }
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return list;
    }
    
public static Vector viewSpecific(String cname, String val)throws SQLException{
        Vector<RoomTransactionBean> list=new Vector<RoomTransactionBean>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from ROOM_TRANSACTION rt left join HOSTEL h on rt.HOSTEL_ID=h.HOSTEL_ID left join ROOM r on rt.ROOM_ID=r.ROOM_ID left join ADMISSION a on rt.ADMISSION_ID=a.ADMISSION_ID left join users u on rt.USER_ID=u.USER_ID where a."+cname+"='"+val+"'");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                RoomTransactionBean rtb=new RoomTransactionBean();
                HostelBean hb=new HostelBean();
                RoomBean rb=new RoomBean();
                AdmissionBean ab=new AdmissionBean();
                UserBean ub=new UserBean();
                rtb.setRoomtransId(rs.getInt(1));
                rtb.setHostelId(rs.getInt(2));
                rtb.setRoomId(rs.getInt(3));
                rtb.setRoomStatus(rs.getInt(4));
                rtb.setAdmissionId(rs.getInt(5));
                rtb.setDoa(rs.getDate(6));
                rtb.setEntryDate(rs.getDate(7));
                rtb.setUserId(rs.getInt(8));
                rtb.setWithdrawDate(rs.getDate(9));
                hb.setHotelId(rs.getInt(10));
                hb.setHostelName(rs.getString(11));
                hb.setHostelType(rs.getString(12));
                hb.setHostelAddress(rs.getString(13));
                hb.setWardenId(rs.getInt(14));
                hb.setMobile(rs.getString(15));
                hb.setPhone(rs.getString(16));
                hb.setEntryDate(rs.getDate(17));
                rtb.setHostelBean(hb);
                rb.setRoomId(rs.getInt(18));
                rb.setHostelId(rs.getInt(19));
                rb.setRoomNo(rs.getString(20));
                rb.setBedNumber(rs.getString(21));
                rb.setBedStatus(rs.getInt(22));
                rb.setEntryDate(rs.getDate(23));
                rb.setUniqueRoomKey(rs.getString(24));
                rb.setFees(rs.getInt(25));
                rtb.setRoomBean(rb);
                ab.setAddmissionId(rs.getInt(26));
                ab.setStudentEmail(rs.getString(27));
                ab.setStudentMobile(rs.getString(28));
                ab.setRollNumber(rs.getString(29));
                ab.setStudentSession(rs.getString(30));
                ab.setCourseId(rs.getInt(31));
                ab.setApplicationNo(rs.getString(32));
                ab.setAdmissionDate(rs.getDate(33));
                ab.setEntryDate(rs.getDate(34));
                ab.setEntryEmail(rs.getString(35));
                ab.setSemester(rs.getInt(36));
                rtb.setAdmissionBean(ab);
                ub.setUserId(rs.getInt(37));
                ub.setDor(rs.getDate(38));
                ub.setPassword(rs.getString(39));
                ub.setUserType(rs.getString(40));
                ub.setEmail(rs.getString(41));
                ub.setEmployeeName(rs.getString(42));
                ub.setDob(rs.getDate(43));
                ub.setEntryDate(rs.getDate(44));
                ub.setUserStatus(rs.getInt(45));
                ub.setEntryEmail(rs.getString(46));
                rtb.setUserBean(ub);
                list.add(rtb);
            }
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return list;
    }
public static Vector checkAllotedOrNot(int admid)throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from ROOM_TRANSACTION  left join room on ROOM_TRANSACTION.ROOM_ID=ROOM.ROOM_ID where room_transaction_id= (select max(ROOM_TRANSACTION_ID) from ROOM_TRANSACTION where ADMISSION_ID=?)");
        pre.setInt(1, admid);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            RoomTransactionBean rtb=new RoomTransactionBean();
            RoomBean rb=new RoomBean();
            rtb.setRoomtransId(rs.getInt(1));
            rtb.setHostelId(rs.getInt(2));
            rtb.setRoomId(rs.getInt(3));
            rtb.setRoomStatus(rs.getInt(4));
            rtb.setAdmissionId(rs.getInt(5));
            rtb.setDoa(rs.getDate(6));
            rtb.setEntryDate(rs.getDate(7));
            rtb.setUserId(rs.getInt(8));
            rtb.setWithdrawDate(rs.getDate(9));
            rb.setRoomId(rs.getInt(10));
            rb.setHostelId(rs.getInt(11));
            rb.setRoomNo(rs.getString(12));
            rb.setBedNumber(rs.getString(13));
            rb.setBedStatus(rs.getInt(14));
            rb.setEntryDate(rs.getDate(15));
            rb.setUniqueRoomKey(rs.getString(16));
            rb.setFees(rs.getInt(17));
            rtb.setRoomBean(rb);
            list.add(rtb);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector checkPaid(String uniqueNi)throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select sum(total_amount),sum(amount_paid),(sum(total_amount)-sum(amount_paid)) from student_fees_detail where UNIQUE_FEES_DETAIL like '%"+uniqueNi+"'");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            list.add(rs.getFloat(1));
            list.add(rs.getFloat(2));
            list.add(rs.getFloat(3));
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static int withdraw(RoomTransactionBean rtb)throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into ROOM_TRANSACTION values(roomtransactionid.nextval,?,?,?,?,?,sysdate,?,sysdate)");
            pre.setInt(1, rtb.getHostelId());
            pre.setInt(2, rtb.getRoomId());
            pre.setInt(3, rtb.getRoomStatus());
            pre.setInt(4, rtb.getAdmissionId());
            pre.setDate(5, null);
            pre.setInt(6, rtb.getUserId());
            i=pre.executeUpdate();
        }catch(Exception e){
            System.out.println(e);
        }finally{
           con.close();
        }return i;
    }
public static Vector viewAllotedRoomDetail(int admid)throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select room.room_no, room.bed_number, hostel.hostel_name, room.hostel_id, ROOM_ID, BED_STATUS from room left join hostel on room.hostel_id=hostel.hostel_id where room_id=(select room_id from room_transaction where room_transaction_id=(select max(room_transaction_id) from room_transaction where admission_id=? and room_status=1))");
        pre.setInt(1, admid);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            list.add(rs.getString(1));
            list.add(rs.getString(2));
            list.add(rs.getString(3));
            list.add(rs.getInt(4));
            list.add(rs.getInt(5));
            list.add(rs.getInt(6));
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector RoomDateWiseR(String from, String to)throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from ROOM_TRANSACTION left join HOSTEL on ROOM_TRANSACTION.HOSTEL_ID=hostel.HOSTEL_ID left join ROOM on ROOM_TRANSACTION.ROOM_ID=room.ROOM_ID left join ADMISSION on ROOM_TRANSACTION.ADMISSION_ID=ADMISSION.ADMISSION_ID left join users on ROOM_TRANSACTION.USER_ID=users.USER_ID where ROOM_TRANSACTION.ENTRY_DATE between to_date(?) and  to_date(?)+1 ");
            pre.setString(1,from);
            pre.setString(2,to);
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                RoomTransactionBean rtb=new RoomTransactionBean();
                HostelBean hb=new HostelBean();
                RoomBean rb=new RoomBean();
                AdmissionBean ab=new AdmissionBean();
                UserBean ub=new UserBean();
                rtb.setRoomtransId(rs.getInt(1));
                rtb.setHostelId(rs.getInt(2));
                rtb.setRoomId(rs.getInt(3));
                rtb.setRoomStatus(rs.getInt(4));
                rtb.setAdmissionId(rs.getInt(5));
                rtb.setDoa(rs.getDate(6));
                rtb.setEntryDate(rs.getDate(7));
                rtb.setUserId(rs.getInt(8));
                rtb.setWithdrawDate(rs.getDate(9));
                hb.setHotelId(rs.getInt(10));
                hb.setHostelName(rs.getString(11));
                hb.setHostelType(rs.getString(12));
                hb.setHostelAddress(rs.getString(13));
                hb.setWardenId(rs.getInt(14));
                hb.setMobile(rs.getString(15));
                hb.setPhone(rs.getString(16));
                hb.setEntryDate(rs.getDate(17));
                rtb.setHostelBean(hb);
                rb.setRoomId(rs.getInt(18));
                rb.setHostelId(rs.getInt(19));
                rb.setRoomNo(rs.getString(20));
                rb.setBedNumber(rs.getString(21));
                rb.setBedStatus(rs.getInt(22));
                rb.setEntryDate(rs.getDate(23));
                rb.setUniqueRoomKey(rs.getString(24));
                rb.setFees(rs.getInt(25));
                rtb.setRoomBean(rb);
                ab.setAddmissionId(rs.getInt(26));
                ab.setStudentEmail(rs.getString(27));
                ab.setStudentMobile(rs.getString(28));
                ab.setRollNumber(rs.getString(29));
                ab.setStudentSession(rs.getString(30));
                ab.setCourseId(rs.getInt(31));
                ab.setApplicationNo(rs.getString(32));
                ab.setAdmissionDate(rs.getDate(33));
                ab.setEntryDate(rs.getDate(34));
                ab.setEntryEmail(rs.getString(35));
                ab.setSemester(rs.getInt(36));
                rtb.setAdmissionBean(ab);
                ub.setUserId(rs.getInt(37));
                ub.setDor(rs.getDate(38));
                ub.setPassword(rs.getString(39));
                ub.setUserType(rs.getString(40));
                ub.setEmail(rs.getString(41));
                ub.setEmployeeName(rs.getString(42));
                ub.setDob(rs.getDate(43));
                ub.setEntryDate(rs.getDate(44));
                ub.setUserStatus(rs.getInt(45));
                ub.setEntryEmail(rs.getString(46));
                rtb.setUserBean(ub);
                list.add(rtb);
            }
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return list;
    }
}
